package dblayer;

import java.sql.*;
import java.util.ArrayList;

import modellayer.Customer;
import modellayer.Invoice;
import modellayer.SalesOrder;

/**
 * 
 * @author Gruppe 3
 *
 */
public class DBSalesOrder implements IFDBSalesOrder {

	private Connection con;

	public DBSalesOrder()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	/**
 	 * A arraylist with all SalesOrders
 	 */
	public ArrayList<SalesOrder> getAllSalesOrders(boolean retriveAssociation)  {
		return miscWhere("", retriveAssociation);
	}

	/**
     * Select a single SalesOrder
     */
	public SalesOrder selectSingleSalesOrder(int salesOrderID, boolean retriveAssociation) throws SQLException {
		String wClause = "salesOrderID = " +salesOrderID;
        return singleWhere(wClause, retriveAssociation);
    }

	/**
     * Insert a SalesOrder to the DB
     */
	public int insertSalesOrder(SalesOrder so) throws SQLException 
    {
   	 PreparedStatement pstmt = null;
   	 int controlInt = -1;
   	 String insert = "insert into SalesOrder(date, deliveryDate, deliveryStatus)"
                      + "values (?, ?, ?)";
   	 //System.out.println(insert);
   	 try {
   		 pstmt = con.prepareStatement(insert);
   		 pstmt.setString(1, so.getDate());
   		 pstmt.setString(2, so.getDeliveryDate());
         pstmt.setString(3, so.getDeliveryStatus());
            controlInt = pstmt.executeUpdate();
        } catch (SQLException sqlE) {
            System.out.println("SQL Error, SalesOrder not inserted");
            System.out.println(sqlE.getMessage());
        } catch (Exception e) {
            e.getMessage();
        }
   	 
   	 return controlInt;
    }

	/**
     * Updates the SalesOrder db
     */
	public int updateSalesOrder(SalesOrder so) throws SQLException {
   	 
   	 int controlInt = -1;

   	 String update = "UPDATE SalesOrder SET "
   	 		+ "date = ? "
   	 		+ "deliveryDate = ?"
   	 		+ "deliveryStatus = ?"
   	 		+ "WHERE salesOrderID = ?";

   	 //System.out.println(update);
   	 try {
   		 PreparedStatement pstmt = null;
   		 pstmt = con.prepareStatement(update);
   		 pstmt.setString(1, so.getDate());
   		 pstmt.setString(2, so.getDeliveryDate());
         pstmt.setString(3, so.getDeliveryStatus());
   		 controlInt = pstmt.executeUpdate();
   	 } catch (SQLException sqlE) {
   		 System.out.println("SQL Error, SalesOrder not updated");
   		 System.out.println(sqlE.getMessage());
   	 } catch (Exception e) {
   		 e.getMessage();
   	 }
   	 
   	 return controlInt;
    }

	/**
     * Delete a SalesOrder from the database
     */
	public int deleteSalesOrder(SalesOrder so) throws SQLException 
    {
   	 PreparedStatement pstmt = null;
   	 int controlInt = -1;
   	 
   	 String delete = "DELETE FROM SalesOrder WHERE salesOrderID = ?";
   	 //System.out.println(delete);
   	 try {
   		 pstmt = con.prepareStatement(delete);
            pstmt.setInt(1, so.getSalesOrderID());
            controlInt = pstmt.executeUpdate();
        } catch (SQLException sqlE) {
            System.out.println("SQL Error, SalesOrder not deleted");
            System.out.println(sqlE.getMessage());
        } catch (Exception e) {
            e.getMessage();
        }
   	 
   	 return controlInt;
    }
	
	/**
 	 * If more than one SalesOrder is to be selected
 	 * @param wClause
 	 * @return ArrayList with SalesOrder objects
 	 */
	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	 {
   	 ResultSet results;
	     ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	
		
	     String query = buildQuery(wClause);
   
	     try{ // read the invoice from the database
			 Statement stmt = con.createStatement();
		 	 stmt.setQueryTimeout(5);
		 	 results = stmt.executeQuery(query);
		 	
			 while( results.next() ){ // loop through all invoices cities and create them as objects
			     SalesOrder soObj = new SalesOrder();
				 soObj = buildSalesOrder(results);	
		         list.add(soObj);	
			 }//end while
			
	         stmt.close();
	         
	        if(retrieveAssociation) {
	        	for(SalesOrder soObj : list) {
					IFDBInvoice dbInvoice = new DBInvoice();
					int invoiceID  = soObj.getInvoiceID();
					Invoice invoiceObj = dbInvoice.selectSingleInvoice(invoiceID);
					soObj.setInvoice(invoiceObj);
					
					IFDBCustomer dbCustomer = new DBCustomer();
					int customerID  = soObj.getCustomerID();
					Customer customerObj = dbCustomer.selectSingleCustomer(customerID, false);
					soObj.setCustomer(customerObj);
					
	        	}//end for
			}//end if
		 }//end try
	 	 catch(Exception e){
	 	 	System.out.println("Query exception - select: "+e);
		 	e.printStackTrace();
	 	 }
		 return list;
	 }
     
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	 {
	 	ResultSet results;
	 	SalesOrder soObj = new SalesOrder();
		        
	 	String query = buildQuery(wClause);
		//System.out.println(query);
		
		try{ 	
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if( results.next() ){
				soObj = buildSalesOrder(results);
				stmt.close();
				
				if(retrieveAssociation) {
					IFDBInvoice dbInvoice = new DBInvoice();
					int invoiceID  = soObj.getInvoiceID();
					Invoice invoiceObj = dbInvoice.selectSingleInvoice(invoiceID);
					soObj.setInvoice(invoiceObj);
					
					IFDBCustomer dbCustomer = new DBCustomer();
					int customerID  = soObj.getCustomerID();
					Customer customerObj = dbCustomer.selectSingleCustomer(customerID, false);
					soObj.setCustomer(customerObj);
				}
				
			}else{ //No customer found
				soObj = null;
			}
		}//end try	
		
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return soObj;
	 }
	/**
	 * 
	 * @param wClause
	 * @return
	 */
	private String buildQuery(String wClause)
	{
	     String query="SELECT * FROM SalesOrder";
		
	 	if (wClause.length()>0)
	 		query=query+" WHERE "+ wClause;
	 		
	 	return query;
	}
	
	/**
     * Builds a SalesOrder object from the data in the database
     * @param result
     * @return SalesOrder object
     * @throws SQLException
     */
    private SalesOrder buildSalesOrder(ResultSet result) throws SQLException {
   	 SalesOrder soObj = new SalesOrder();

   	 try {
   		 soObj.setSalesOrderID(result.getInt("salesOrderID"));
   		 soObj.setDate(result.getString("date"));
   		 soObj.setDeliveryDate(result.getString("deliveryDate"));
   		 soObj.setDeliveryStatus(result.getString("deliveryStatus"));
   		 soObj.setInvoiceID(result.getInt("invoiceID"));
   		 soObj.setCustomerID(result.getInt("customerID"));
   	 } catch (Exception e) {
   		 System.out.println("building SalesOrder object");
        }
        return soObj;
    }
    
    public int getMaxID() {
    	ResultSet results;
    	int id = -1;
    	try{ 
    	   Statement stmt = con.createStatement();
    	   String query = "Select max(SalesOrderID) from SalesOrder";
    	   results = stmt.executeQuery(query);
    	   if( results.next() ){
    		 id = results.getInt(1);
    	    }
    	}	
    	catch(Exception e){
    	    System.out.println("Query exception: Error in reading maxid" + e);
    	}
    	return id;
    }
}
